package com.lingxu.base.system.common.controller;

import com.baomidou.mybatisplus.extension.service.IService;
import com.lingxu.base.common.annotation.ExcelImportExport;
import com.lingxu.base.common.exception.BussException;
import com.lingxu.base.system.common.mapper.LingXuMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public abstract class LingXuController<T,S extends IService<T>> {
    // 导入导出需要的参数
    // entity中的字段名
    private List<String> entityNameList;
    // excel中的列名
    private List<String> excelNameList;
    // 导出列排序
    private List<Integer> exportOrderList;
    // 导出翻译用字典
    private Map<String,String> exportMap;
    // 导入翻译用字典
    private Map<String,String> importMap;
    // 需要翻译的列的集合
    private Set<String> dictSet;

    @Autowired
    S service;

    @Autowired
    private LingXuMapper lingXuMapper;

    /**
     * 导入
     * @param request
     * @param clazz
     * @return
     */
    protected void importExcel(HttpServletRequest request, Class<T> clazz){
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        MultipartFile multipartFile = multipartRequest.getFile("file");
        Workbook wb = null;
        try {

            InputStream is=multipartFile.getInputStream();
            FileInputStream fis = (FileInputStream) is;

            // 获得文件名称
            String fileName = multipartFile.getOriginalFilename();
            // 获得后缀
            String extName = fileName.substring(fileName.indexOf(".")+1);
            // 根据后缀名称判断excel的版本
            if("xlsx".equals(extName)) {
                wb = new XSSFWorkbook(fis);
            }else if("xls".equals(extName)){
                wb = new HSSFWorkbook(fis);
            } else {
                throw new BussException("无效的Excel版本");
            }
            List<List<String>> sheetList=new ArrayList<>();
            Sheet sheet=wb.getSheetAt(0);
            setCellMeaning(clazz);
            List<T> dataList=copyFromSheetToList(sheet,clazz);
            service.saveBatch(dataList);

        }catch(Exception e){
            throw new BussException("导入出现异常："+e.getMessage());
        }

    }

    /**
     * 导出excel
     *
     * @param dataList 导出的结果集
     * @param exportFileName 导出文件名或sheet名
     * @param response 响应流
     * @param clazz
     */
    public void exportXls(List<T> dataList, String exportFileName, HttpServletResponse response, Class<T> clazz) throws NoSuchFieldException, IllegalAccessException, UnsupportedEncodingException {
        setCellMeaning(clazz);
        // 去除不需要导出的列
        for(int i=exportOrderList.size();i>0;i--){
            if(exportOrderList.get(i-1)==0){
                exportOrderList.remove(i-1);
                excelNameList.remove(i-1);
                entityNameList.remove(i-1);
            }
        }

        HSSFWorkbook workbook=new HSSFWorkbook();

        //创建Excel工作表对象
        HSSFSheet sheet = workbook.createSheet(exportFileName);
        HSSFRow row = sheet.createRow(0);
        // 表头
        for(int i=0;i<entityNameList.size();i++){
            row.createCell(exportOrderList.get(i)-1).setCellValue(excelNameList.get(i));
        }

        for(int i=1;i<=dataList.size();i++){
            HSSFRow rowt = sheet.createRow(i);
            for(int j=0;j<entityNameList.size();j++){
                Field field=clazz.getDeclaredField(entityNameList.get(j));
                field.setAccessible(true);
                String cellValueStr=String.valueOf(field.get(dataList.get(i-1)));
                if(dictSet.contains(entityNameList.get(j))){
                    // 需要使用字典翻译
                    cellValueStr=exportMap.get(entityNameList.get(j)+"_"+cellValueStr);
                }
                rowt.createCell(exportOrderList.get(j)-1).setCellValue(cellValueStr);
            }
        }

        //文档输出
        exportFileName= URLEncoder.encode(exportFileName, "utf-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename="+exportFileName+".xls");
        try {
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }catch (Exception e){
            log.error("导出文件-文件写入出错"+e.getMessage());
        }
    }

    /**
     * 根据entity中的注解，将要导入导出的字段信息取出来
     * @param clazz
     */
    private void setCellMeaning(Class<T> clazz){
        entityNameList=new ArrayList<>();
        excelNameList=new ArrayList<>();
        exportOrderList=new ArrayList<>();
        exportMap=new HashMap<>();
        importMap=new HashMap<>();
        dictSet=new HashSet<>();

        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            ExcelImportExport anno = field.getAnnotation(ExcelImportExport.class);
            if (anno != null) {
                entityNameList.add(field.getName());
                excelNameList.add(anno.name());
                exportOrderList.add(anno.exportOrderBy());
                if ("".equals(anno.sql()) && "".equals(anno.dictCode())) {
                    continue;
                }
                String sql = "";
                if (!"".equals(anno.sql())) {
                    // 优先使用sql
                    sql = anno.sql();
                } else {
                    // 使用字典表
                    sql = "SELECT VALUE AS KEY,NAME AS VALUE FROM SYS_DICT_ITEM WHERE DICT_ID=(SELECT ID FROM SYS_DICT WHERE CODE='" + anno.dictCode() + "')";
                }
                List<Map<String, Object>> mapList = lingXuMapper.execSql(sql);
                for (Map<String, Object> map : mapList) {
                    dictSet.add(field.getName());
                    exportMap.put(field.getName() + "_" + map.get("KEY"), String.valueOf(map.get("VALUE")));
                    importMap.put(field.getName() + "_" + map.get("VALUE"), String.valueOf(map.get("KEY")));
                }
            }
        }

    }

    /**
     * 导入时将sheet页中的数据转换成List<T>
     * @param sheet
     * @param clazz
     * @return
     */
    private List<T> copyFromSheetToList(Sheet sheet ,  Class<T> clazz){

        // 根据cellMeaning 做成 index,column对照map
        Map<Integer,String> cellMeanIndexMap=new HashMap<>();
        // 将excel中的列与entity属性创建对照关系
        Row nameRow=sheet.getRow(0);
        for(int i=0;i<entityNameList.size();i++){
            for(int j=0;j<nameRow.getLastCellNum();j++){
                if(cellToString(nameRow.getCell(j)).equals(excelNameList.get(i))){
                    cellMeanIndexMap.put(j,entityNameList.get(i));
                    break;
                }
            }
        }
        //
        List<T> dataList=new ArrayList<>();
        Field field=null;
        try {
            for(int i=1;i<sheet.getLastRowNum() + 1;i++){
                T data=clazz.newInstance();
                for(int j=0;j<sheet.getRow(i).getLastCellNum();j++){
                    if(cellMeanIndexMap.containsKey(new Integer(j))){
                        field = clazz.getDeclaredField(cellMeanIndexMap.get(j));
                        field.setAccessible(true);
                        Cell cell=sheet.getRow(i).getCell(j);
                        ExcelImportExport anno=field.getAnnotation(ExcelImportExport.class);
                        if("".equals(anno.sql())&&"".equals(anno.dictCode())){
                            // 不需要翻译
                            field.set(data,importFormat(cellToString(cell),field.getType().getName(),cell.getCellTypeEnum()));
                        }else{
                            field.set(data,importFormatByValue2Key(cellToString(cell),field.getType().getName(),field.getName(),importMap));
                        }
                    }
                }
                dataList.add(data);
            }
        }catch(Exception e){
            throw new BussException("Sheet转换Entity异常："+e.getMessage());
        }

        return dataList;
    }

    /**
     * 将Cell转换为对应Entity中Filed的格式，且根据字典翻译
     * @param value 单元格中的值
     * @param parmtype 要转成的entity类型
     * @param fieldName 要转换的列
     * @param importMap 字典Map
     * @return obj 转换并翻译后的值
     */
    private Object importFormatByValue2Key(String value, String parmtype, String fieldName,Map<String,String> importMap){
        Object obj = new Object();
        if (parmtype.equals("java.lang.Integer"))
        {
            obj = Integer.valueOf(importMap.get(fieldName+"_"+value));
        } else if(parmtype.equals("java.lang.String"))
        {
            obj = importMap.get(fieldName+"_"+value);
        }else{
            throw new BussException("字典翻译只能将文字翻译成字符串或数字");
        }
        return obj;

    }

    /**
     * 将Cell转换为对应Entity中Filed的格式
     * @param value  单元格中的值
     * @param parmtype  要转成的entity类型
     * @param cellType 来源单元格类型
     * @return obj 转换后的值
     * @throws ParseException
     */
    private Object importFormat(String value, String parmtype, CellType cellType) throws ParseException
    {
        Object obj = new Object();
        if (parmtype.equals("java.lang.Integer"))
        {
            Double d = Double.parseDouble(value);
            obj = d.intValue();
        } else if (parmtype.equals("java.lang.Double"))
        {
            obj = Double.parseDouble(value);
        } else if (parmtype.equals("java.lang.Float"))
        {
            Double d = Double.parseDouble(value);
            obj = d.floatValue();
        } else if (parmtype.equals("java.lang.Long"))
        {
            Double d = Double.parseDouble(value);
            obj = d.longValue();
        } else if (parmtype.equals("java.util.Date"))
        {
            if (cellType == CellType.NUMERIC)
            {
                Double d = Double.parseDouble(value);
                Date date = DateUtil.getJavaDate(d);
                obj = date;
            } else
            {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                obj = sdf.parse(value);
            }
        } else if (parmtype.equals("java.math.BigDecimal"))
        {
            BigDecimal ab = new BigDecimal(value);
            obj = ab;
        } else
        {
            obj = value;
        }
        return obj;

    }

    /**
     * 单元格转字符串
     * @param cell
     * @return
     */
    private String cellToString(Cell cell){

        if(cell==null){
            return null;
        }else{
            if (cell.getCellTypeEnum() == CellType.STRING) {
                return cell.getStringCellValue();
            } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                return String.valueOf(cell.getNumericCellValue());
            } else if (cell.getCellTypeEnum() == CellType.BLANK) {
                return null;
            }else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                return null;
            } else {
                throw new BussException("Excel格式不正确");
            }
        }
    }

}
